Introduction to Subqueries in MySQL

Course: MySQL Training

Topic: Introduction to Subqueries in MySQL

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to Subqueries in MySQL

A subquery in MySQL is a SQL query written inside another SQL query. It is also called an inner query or nested query. The query containing the subquery is called the outer query. A subquery allows you to retrieve information from one table and use that information to filter, compare, or calculate results in another query.

Subqueries are useful when a database operation requires an intermediate result. Instead of executing multiple separate queries manually, you can combine them into a single SQL statement.

For example, suppose a company wants to find employees who earn more than the average salary of all employees. First, the average salary must be calculated. A subquery can calculate that average, while the outer query retrieves the employees whose salaries exceed it.

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

In this example, the inner query calculates the average salary, and the outer query selects employees earning more than that average.

2. Learning Objectives

After studying this topic, you should be able to:

• Understand the meaning and purpose of subqueries in MySQL.
• Differentiate between inner queries and outer queries.
• Understand where subqueries can be used in SQL statements.
• Write subqueries using SELECT, WHERE, and other supported clauses.
• Use comparison operators with subqueries.
• Understand single-row and multiple-row subqueries.
• Use IN, NOT IN, EXISTS, ANY, and ALL with subqueries.
• Understand correlated and non-correlated subqueries.
• Identify common subquery errors and troubleshoot them.
• Recognize when a JOIN or another query structure may be more suitable.

3. What Is a Subquery?

A subquery is a query embedded inside another SQL statement. It is usually enclosed in parentheses and provides a result that the outer query can use.

General syntax:

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

The inner query retrieves or calculates information. The outer query uses that result to determine which records to return or what values to process.

3.1 Example of a Simple Subquery

Suppose a table named employees contains employee names, salaries, and department IDs. To find employees who work in department 10, you could use a subquery to retrieve the department ID from a separate departments table.

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Finance'
);

The inner query finds the department ID for Finance. The outer query retrieves employees assigned to that department. This example assumes the department name identifies exactly one department.

4. Understanding Inner Queries and Outer Queries

A nested SQL statement has two main parts: the inner query and the outer query.

4.1 How a Basic Subquery Works

1. The inner query retrieves or calculates the required information.
2. The result is made available to the outer query.
3. The outer query evaluates its conditions using that result.
4. MySQL returns the rows that satisfy the outer query.

This describes the logical relationship between the queries. MySQL's optimizer may transform or execute the statement differently internally to improve performance.

5. Preparing Sample Tables for Practice

Before writing subqueries, create sample tables and insert some data. The following examples use an employee table and a department table.

5.1 Create the Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

5.2 Insert Department Records

INSERT INTO departments
    (department_id, department_name, location)
VALUES
    (10, 'Finance', 'Mumbai'),
    (20, 'IT', 'Pune'),
    (30, 'HR', 'Delhi'),
    (40, 'Marketing', 'Bengaluru');

5.3 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2),
    department_id INT,
    manager_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

5.4 Insert Employee Records

INSERT INTO employees
    (employee_id, employee_name, salary, department_id, manager_id)
VALUES
    (1, 'Amit', 60000, 10, NULL),
    (2, 'Priya', 75000, 20, NULL),
    (3, 'Rahul', 45000, 10, 1),
    (4, 'Sneha', 90000, 20, 2),
    (5, 'Neha', 50000, 30, NULL),
    (6, 'Vikas', 65000, 30, 5),
    (7, 'Anita', 40000, 40, NULL);

These tables will be used throughout the notes to demonstrate how subqueries work with real examples.

6. Why Do We Use Subqueries in MySQL?

Subqueries make it possible to solve queries that depend on information retrieved from the same database. They can help express complex conditions in a readable and organized way.

• Compare against calculated values: Find employees earning above the average salary.
• Filter using another table: Find employees working in the Finance department.
• Check whether related records exist: Find departments that have employees.
• Retrieve values dynamically: Find employees earning the maximum salary.
• Support conditional logic: Use a subquery inside an expression such as CASE or WHERE.
• Perform nested calculations: Use the result of one query in another calculation.

7. Subqueries with the WHERE Clause

The WHERE clause is one of the most common places to use a subquery. It allows the outer query to filter records based on a value or set of values returned by the inner query.

7.1 Find Employees Earning More Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Explanation: The subquery calculates the average salary across all employees. The outer query returns only employees whose salary is greater than that average.

The current sample salaries total 425000 across seven employees, so the average is approximately 60714.29. Employees with salaries above this amount are included in the result.

7.2 Find Employees Earning the Maximum Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The inner query finds the highest salary. The outer query returns every employee whose salary equals that value. Using equality against the maximum also allows multiple employees to be returned if they share the highest salary.

7.3 Find Employees Earning Less Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
);

This query returns employees whose salaries are below the average salary calculated by the inner query.

8. Subqueries with Comparison Operators

Comparison operators allow the outer query to compare a column with the result of a subquery. A comparison such as = generally requires a single value, whereas operators such as > and < also compare a value against one scalar result.

8.1 Example Using Greater Than or Equal To

SELECT employee_name, salary
FROM employees
WHERE salary >= (
    SELECT AVG(salary)
    FROM employees
);

This query returns employees whose salaries are equal to or greater than the calculated average.

8.2 Important Rule: Single-Value Comparisons

If a subquery used with =, >, or another ordinary scalar comparison returns multiple rows, MySQL may report an error such as Subquery returns more than 1 row.

For example, the following query is unsafe if multiple departments match the specified location:

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Pune')
);

Because the inner query may return multiple department IDs, use IN when multiple results are intended:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Pune')
);

9. Subqueries with the IN Operator

The IN operator checks whether a value matches any value in a list or result set. It is especially useful when the subquery returns multiple rows.

9.1 Find Employees Working in Selected Departments

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('Finance', 'IT')
);

The inner query returns the IDs of the Finance and IT departments. The outer query retrieves employees whose department IDs appear in that result.

9.2 Find Employees Not in Selected Departments

SELECT employee_name, department_id
FROM employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('Finance', 'IT')
);

This query returns employees whose department IDs do not match those returned by the inner query.

Important: If the subquery used with NOT IN returns a NULL value, the comparison may evaluate to UNKNOWN and unexpectedly exclude rows. When NULLs are possible, consider NOT EXISTS or explicitly exclude NULLs from the subquery result.

10. Single-Row and Multiple-Row Subqueries

Subqueries can be classified according to the number of rows they return.

10.1 Example of a Single-Row Subquery

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The aggregate function MAX returns one value, making it suitable for a scalar comparison.

10.2 Example of a Multiple-Row Subquery

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Pune', 'Delhi')
);

The inner query can return multiple department IDs. IN checks whether each employee's department ID matches any of them.

11. Subqueries with ANY and ALL

ANY and ALL allow a comparison operator to evaluate a value against the set of values returned by a subquery.

11.1 Using ANY

ANY means that the comparison must be true for at least one value returned by the subquery. In MySQL, SOME is a synonym for ANY.

SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 10
);

This query finds employees whose salaries are greater than at least one salary in department 10.

11.2 Using ALL

ALL means that the comparison must be true for every value returned by the subquery.

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 10
);

This query finds employees whose salaries are greater than every salary returned for department 10.

Remember that NULL values can affect comparisons, and an empty subquery result has special truth-value behavior. Use these operators carefully when NULLs or empty result sets are possible.

12. Subqueries with EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns at least one row. It is commonly used to test whether related records exist in another table. NOT EXISTS checks whether the subquery returns no rows.

12.1 Find Departments That Have Employees

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

The subquery checks whether at least one employee belongs to each department. Departments with matching employee records are returned.

12.2 Find Departments That Have No Employees

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

This query returns departments for which no matching employee record exists. With the sample data, the Marketing department has an employee, so it will not be included. A department with no assigned employees would be included.

12.3 Why SELECT 1 Is Used with EXISTS

In an EXISTS subquery, MySQL is concerned with whether a row exists, not with the actual values in the SELECT list. Therefore, SELECT 1 is a common convention. You may also see SELECT * used with EXISTS.

13. Non-Correlated Subqueries

A non-correlated subquery is independent of the outer query. It does not refer to a column from the outer query. Its result can be determined without using a value from the current outer row.

13.1 Example

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary without referencing a specific row from the outer query. The average is then used as the threshold for selecting employees.

13.2 Characteristics of Non-Correlated Subqueries

• The inner query does not depend on the current row of the outer query.
• It is generally easier to read and test independently.
• It can return a scalar value, a set of values, or a result set depending on its context.
• MySQL's optimizer determines the most suitable execution strategy.

14. Correlated Subqueries

A correlated subquery references one or more columns from the outer query. Its logic depends on the current outer row. MySQL may evaluate the subquery for multiple outer rows, although the optimizer can sometimes transform the query to improve performance.

14.1 Find Employees Earning More Than Their Department Average

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

The inner query calculates the average salary for the department associated with the current employee. The outer query returns employees whose salaries are higher than their own department's average.

14.2 How the Correlation Works

1. The outer query identifies an employee row.
2. The inner query uses that employee's department ID.
3. The inner query calculates the average salary for the matching department.
4. The outer query compares the employee's salary against that department average.
5. Employees who satisfy the condition are returned.

14.3 Advantages and Disadvantages

15. Subqueries in the SELECT Clause

A scalar subquery can be used in the SELECT list to return a calculated value alongside each row. The subquery must return at most one row and one column when used as a scalar expression.

15.1 Display the Average Salary Alongside Each Employee

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS average_salary
FROM employees;

The average salary is displayed in a separate column for each employee. The value is the same for every row because the inner query calculates the overall average.

15.2 Display the Total Number of Employees

SELECT
    employee_name,
    salary,
    (SELECT COUNT(*) FROM employees) AS total_employees
FROM employees;

This query adds a column showing the total number of employees in the table. Because the subquery is independent of each outer row, the count is repeated for every employee.

16. Subqueries in the FROM Clause

A subquery used in the FROM clause produces a derived table. A derived table acts like a temporary result set for the duration of the query. In MySQL, a derived table must have a table alias.

16.1 Calculate Average Salary by Department

SELECT
    department_summary.department_id,
    department_summary.average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary;

The inner query groups employees by department and calculates the average salary. The outer query reads the resulting department summary as a derived table.

16.2 Filter Departments by Average Salary

SELECT
    department_summary.department_id,
    department_summary.average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary
WHERE department_summary.average_salary > 60000;

This query first constructs a department-level summary and then returns only departments whose average salary is greater than 60000.

17. Subqueries in the HAVING Clause

The HAVING clause filters grouped results. A subquery can be used in HAVING when a group-level result needs to be compared with a value calculated by another query.

17.1 Find Departments Whose Average Salary Is Above the Overall Average

SELECT
    department_id,
    AVG(salary) AS department_average
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary for the entire company. The outer query groups employees by department and keeps only departments whose average exceeds the company-wide average.

WHERE versus HAVING: WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. Choose the clause that matches the condition you need to apply.

18. Subqueries with Aggregate Functions

Aggregate functions summarize data and are frequently used inside subqueries. Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().

18.1 Find Employees Earning More Than the Minimum Salary in IT

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE department_name = 'IT'
    )
);

This example contains a nested subquery inside another subquery. The innermost query finds the IT department ID. The next query calculates the minimum salary for employees in that department. The outer query retrieves employees earning more than that minimum.

This structure assumes that the department name identifies one department. If department names are not unique, use a query structure that explicitly handles multiple matching department IDs.

19. Nested Subqueries

A nested subquery is a subquery inside another subquery. Multiple levels of nesting can be used when one intermediate result depends on another.

19.1 Example of a Nested Subquery

SELECT employee_name, salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location = 'Mumbai'
      AND department_name = 'Finance'
);

The inner query identifies the Finance department located in Mumbai. The outer query retrieves employees assigned to that department.

19.2 Nested Subquery Best Practices

• Use meaningful table aliases to make relationships clear.
• Keep nesting levels as low as practical.
• Test inner queries separately before combining them.
• Check whether a subquery may return multiple rows.
• Use JOINs or derived tables when they make the logic clearer.
• Review query performance on realistic amounts of data.

20. Subqueries and JOINs: Key Differences

Subqueries and JOINs are both used to retrieve information across tables, but they express different query structures. A subquery embeds one query within another, while a JOIN combines rows from tables based on a related condition.

20.1 Example Using a Subquery

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Finance'
);

20.2 Equivalent Example Using a JOIN

SELECT e.employee_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_name = 'Finance';

Both examples can return the employees assigned to Finance when department names and relationships satisfy the assumptions. The JOIN is often more convenient when you also need department columns. The subquery can be convenient when the goal is simply to filter employees by a set of department IDs.

Neither approach is always faster. Compare execution plans and actual performance before choosing between them for a large or frequently executed query.

21. Common Errors When Using Subqueries

21.1 Error: Subquery Returns More Than One Row

Problem: A scalar comparison expects a single value, but the subquery returns multiple rows.

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
);

Solution: If multiple department IDs are expected, use IN:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
);

21.2 Error: Unknown Column

Problem: A column name is misspelled or referenced using an incorrect table alias.

Solution: Verify column names, table definitions, and aliases. For correlated subqueries, ensure that outer query columns are referenced using the correct alias.

21.3 Error: Missing Parentheses

Problem: The subquery is not enclosed correctly.

Solution: Check that the opening and closing parentheses match and that the inner query is positioned in a valid part of the outer statement.

21.4 Error: Incorrect Use of Aggregate Functions

Problem: An aggregate condition is applied in WHERE instead of HAVING, or the query mixes grouped and non-grouped columns incorrectly.

Solution: Use WHERE for row-level conditions and HAVING for conditions on grouped results. Make sure the SELECT list follows MySQL's grouping rules.

21.5 Unexpected Results from NOT IN

Problem: The subquery includes NULL, which can cause comparisons to evaluate to UNKNOWN.

Solution: Exclude NULL values when appropriate or use a NOT EXISTS query that expresses the intended relationship.

SELECT e.employee_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

This example finds employees whose department ID does not match any department ID. If the foreign key is enforced and department IDs are not NULL, such rows should normally not exist. The example is still useful for understanding the NOT EXISTS pattern.

22. Performance Optimization for Subqueries

Subqueries can be efficient, but performance depends on the query structure, table size, indexes, and MySQL's optimizer. Follow these practices when working with large databases.

• Index lookup columns: Index columns used to match records, such as employees.department_id.
• Filter early where appropriate: Write conditions that reduce unnecessary processing without changing the intended result.
• Avoid unnecessary nesting: Simplify queries when a direct expression, JOIN, or grouped query is easier to maintain.
• Use EXISTS for existence checks: EXISTS expresses the requirement that at least one matching row exists.
• Inspect execution plans: Use EXPLAIN to understand how MySQL plans to execute a query.
• Test with realistic data: Small sample tables may not reveal performance issues that occur at scale.
• Return only necessary columns: Avoid retrieving columns that the application does not need.
• Check correlated conditions: Ensure correlated subqueries use appropriate indexes and do not perform unnecessary work.

22.1 Using EXPLAIN

EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

EXPLAIN displays information about the execution plan. Depending on the MySQL version and query type, it can help you inspect access methods, possible indexes, selected keys, estimated rows, and other plan details.

Do not assume that every subquery is executed separately for every row or that every JOIN is faster. MySQL can optimize different query structures in different ways.

23. Practical Project: Employee Salary Analysis Using Subqueries

In this practical exercise, you will use subqueries to answer common employee-management questions.

Task 1: Find Employees Earning More Than the Company Average

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Task 2: Find the Highest-Paid Employee

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

Task 3: Find Employees in the Finance Department

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Finance'
);

Task 4: Find Employees Earning More Than Their Department Average

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Task 5: Find Departments Without Employees

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

Task 6: Display the Overall Employee Count Alongside Each Employee

SELECT
    employee_name,
    (SELECT COUNT(*) FROM employees) AS total_employees
FROM employees;

Project Learning Outcomes

• Use aggregate functions inside subqueries.
• Filter data using a single returned value.
• Filter data using a set of returned values.
• Compare employees with department-level averages.
• Check for the presence or absence of related records.
• Choose between subqueries and JOINs based on the task.

24. Practice Exercises

Complete these exercises using the sample employees and departments tables.

1. Write a query to find employees whose salaries are greater than the minimum salary in the company.
2. Write a query to find employees earning exactly the maximum salary.
3. Find employees who work in the IT department using a subquery.
4. Find employees whose salaries are below the company average.
5. Find employees whose salaries are greater than every salary in department 30.
6. Use EXISTS to find departments with at least one employee.
7. Use NOT EXISTS to find departments without employees.
8. Write a correlated subquery that finds employees earning more than their department average.
9. Use a derived table to calculate the average salary for each department.
10. Rewrite a subquery using a JOIN and compare the results.

Sample Solution: Exercise 1

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT MIN(salary)
    FROM employees
);

Sample Solution: Exercise 3

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

Sample Solution: Exercise 6

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

25. Interview Questions and Answers

Q1. What is a subquery in MySQL?

A subquery is a SQL query embedded inside another SQL statement. It provides a value or result set that the outer query can use.

Q2. What is the difference between an inner query and an outer query?

The inner query is the nested query. The outer query contains the subquery and uses its result to retrieve or filter data.

Q3. Where can subqueries be used?

Subqueries can appear in supported contexts such as SELECT, WHERE, HAVING, and FROM. They can also be used in other SQL statements where MySQL permits a subquery.

Q4. What is a scalar subquery?

A scalar subquery returns one column and at most one row, producing a single value when it returns a row. If it returns no rows, its value is NULL. Returning multiple rows in a scalar context causes an error.

Q5. What is a correlated subquery?

A correlated subquery refers to a column from its outer query. Its result depends on the current outer row.

Q6. What is a non-correlated subquery?

A non-correlated subquery does not refer to the outer query and can be evaluated independently of its current rows.

Q7. What is the difference between IN and EXISTS?

IN checks whether a value matches a value in a list or result set. EXISTS checks whether a subquery returns at least one row. The best choice depends on the query's meaning and execution plan.

Q8. What is the difference between ANY and ALL?

ANY requires the comparison to be true for at least one returned value. ALL requires the comparison to be true for every returned value.

Q9. Can a subquery return multiple rows?

Yes. A subquery can return multiple rows when used in a context that supports them, such as IN, EXISTS, ANY, or ALL. A scalar comparison generally requires a single value.

Q10. Can a subquery be used in the FROM clause?

Yes. A subquery in the FROM clause creates a derived table. In MySQL, the derived table must have an alias.

Q11. Are subqueries always slower than JOINs?

No. Performance depends on the query, indexes, data distribution, and optimizer behavior. Compare execution plans rather than assuming one structure is always faster.

Q12. Why can NOT IN produce unexpected results?

If the subquery returns NULL, NOT IN comparisons can evaluate to UNKNOWN. NOT EXISTS is often a clearer alternative when checking for missing matches.

Q13. What happens when a scalar subquery returns no rows?

Its scalar value is NULL. Comparisons with NULL usually evaluate to UNKNOWN rather than TRUE, so the outer query may return no matching rows.

Q14. Can subqueries be nested?

Yes. One subquery can contain another subquery. However, unnecessary nesting can make SQL harder to read and maintain.

Q15. How can subquery performance be improved?

Use suitable indexes, simplify unnecessary nesting, check the query with EXPLAIN, filter appropriately, and test against realistic data volumes.

26. Quick Revision Checklist

• A subquery is a query nested inside another query.
• The inner query provides a value or result set to the outer query.
• Subqueries are commonly enclosed in parentheses.
• Scalar comparisons require a single value.
• IN is useful when comparing against multiple possible values.
• EXISTS checks whether at least one matching row exists.
• NOT EXISTS checks whether no matching row exists.
• ANY means at least one comparison succeeds.
• ALL means the comparison succeeds for every returned value.
• A correlated subquery references a column from the outer query.
• A non-correlated subquery is independent of the outer query.
• A subquery in FROM creates a derived table that requires an alias.
• Aggregate functions such as AVG(), MIN(), MAX(), and COUNT() are commonly used in subqueries.
• NULL values can affect IN, NOT IN, and comparison results.
• Use EXPLAIN and suitable indexes when evaluating query performance.

27. Conclusion

Subqueries are an important MySQL feature for retrieving, comparing, and analyzing database information. They allow one SQL query to use the results of another query, making it easier to calculate averages, find maximum and minimum values, filter records across related tables, and test whether related rows exist.

To use subqueries effectively, understand the difference between scalar and multiple-row results, learn the roles of IN, EXISTS, ANY, and ALL, and practice correlated and non-correlated query patterns. Always check how NULL values affect your conditions and use EXPLAIN to investigate performance when working with large datasets.

Continue learning MySQL through the MySQL Training Course. You can also register for a course demo to explore the training program.

Additional Reference: Official MySQL Documentation: Subqueries